package com.erhya.export.poi;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.util.CollectionUtils;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * 基于POI实现的模板方法
 * 使用方法： PoiExcelTemplate<对象> poi = PoiExcelTemplate<对象>(若干参数);
 *           poi.execute(参数);
 * @param <T> 数据泛型
 * @author lilin  模板模式 + 泛型 + 反射
 */
public class PoiExcelTemplate<T> extends AbstractExcelTemplate<T> implements ExecuteChain<T> {

    /** excel的文档对象 */
    private Workbook workbook;
    /** excel的表单 */
    private Sheet sheet;
    /** excel的表单名称 */
    private String sheetName;
    /** 标题 */
    private String[][] title;
    /** 标题中最长的一行 */
    private Integer titleMaxLen;
    /** 标题栏的合并列 */
    private List<CellRangeAddress> shellMerge;
    /** 默认单元格宽度 */
    private static Integer DEFAULT_WIDTH = 18;

    /** 导出文件名 */
    private String fileName;
    /** 标题栏 样式 */
    private CellStyle titleStyle;
    /** 数据栏 样式 */
    private CellStyle dataStyle;

    /** 列表数据 */
    private List<T> data;
    /** 列表数据展示字段 */
    private String[] showFiles;
    /** 当前数据行（标题行数+已写入数据行） */
    private Integer startIndex;

    private boolean autoExport;

    public PoiExcelTemplate(String fileName, String[][] title, String sheetName) {
        this.fileName = fileName;
        this.title = title;
        this.sheetName = sheetName;
        this.autoExport = true;
    }

    public PoiExcelTemplate(String fileName, String[][] title, String sheetName, Boolean autoExport) {
        this.fileName = fileName;
        this.title = title;
        this.sheetName = sheetName;
        this.autoExport = autoExport;
    }

    /**
     * 初始化方法
     * @param workbook 工作簿对象
     */
    private void init(Workbook workbook){

        if (workbook == null){
            workbook = new HSSFWorkbook();
        }

        this.workbook = workbook;
        this.titleStyle = buildTitleStyle();

        if (StringUtils.isEmpty(sheetName)){
            sheetName = "Sheet";
        }

        this.sheet = workbook.createSheet(sheetName);

        this.shellMerge = new ArrayList<>();
        super.counter = new AtomicInteger(0);
        this.startIndex = this.title == null ? 0 : this.title.length;
        this.dataStyle = buildColumStyle();
    }

    @Override
    public void execute(List<T> list, String... showFiles){
        this.showFiles = showFiles;
        init(workbook);
        titleInit();
        this.dataStyle = buildColumStyle();
        buildList(list, showFiles);
        if (autoExport){
            exportList();
        }
    }

    @Override
    public void unExecute(List<T> list, String... showFiles){
        this.data = list;
        this.showFiles = showFiles;
    }

    @Override
    public void china(String fileName, Boolean flag){
        if (StringUtils.isNotEmpty(fileName)){
            this.fileName = fileName;
        }
        init(workbook);
        titleInit();
        this.dataStyle = buildColumStyle();
        buildList(data, showFiles);
        if (!flag){
            exportList();
        }
    }

    private void titleInit(){
        if (this.title == null || this.title.length == 0){
            return;
        }

        if (titleMaxLen == null){
            titleMaxLen = 0;
        }

        // 计算最长的一行
        for (String[] t1 : title) {
            int length = t1.length;
            titleMaxLen = Math.max(length, titleMaxLen);
        }

        initMergeTitle();

    }

    private void initMergeTitle(){
        String tag = null;
        for (int i = 0; i < title.length; i++) {
            Row row = this.sheet.createRow(i);
            String[] t2 = title[i];
            if (t2.length == 1 && titleMaxLen > 1){
                Cell cell = row.createCell(0);
                cell.setCellStyle(this.titleStyle);
                cell.setCellType(CellType.STRING);
                cell.setCellValue(t2[0]);
                shellMerge.add(new CellRangeAddress(i, i, 0, titleMaxLen-1));
            } else {
                Integer mergeCell = 0;
                for (int j = 0; j < t2.length; j++) {
                    Cell cell = row.createCell(j);
                    String cell2 = t2[j];
                    if (tag == null || j == 0){
                        tag = cell2;
                    }

                    cell.setCellStyle(this.titleStyle);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(cell2);

                    if (tag.equals(cell2)){
                        mergeCell++;
                    } else {
                        if (mergeCell > 1){
                            this.shellMerge.add(new CellRangeAddress(i, i, j-mergeCell, j-1));
                        }
                        tag = cell2;
                        mergeCell = 1;
                    }

                    // 最后一行合并方式和其他不一样
                    if (j == t2.length-1 && tag.equals(cell2)){
                        if (mergeCell > 1){
                            this.shellMerge.add(new CellRangeAddress(i, i, j-mergeCell+1, j));
                        }
                    }
                }
            }
        }

        for (CellRangeAddress cellAddresses : this.shellMerge) {
            this.sheet.addMergedRegion(cellAddresses);
        }

        this.shellMerge.clear();
    }

    protected void buildList(List<T> list, String... showFiles){
        // 单元格宽度设置一次即可
        Boolean columnFlat = true;
        Integer sheetIndex = 2;
        for (T t : list) {
            obj = t;
            clazz = t.getClass();

            // 判断是否需要拆分多个Sheet
            if (fetchSize != null && startIndex >= (fetchSize + title.length)){
                startIndex = this.title.length;
                columnFlat = true;
                this.sheet = workbook.createSheet(this.sheetName+sheetIndex);
                sheetIndex++;
                initMergeTitle();
            }

            Row row = this.sheet.createRow(startIndex);
            int i = 0;
            for (String showFile : showFiles) {
                // 获取字段值
                String fieldVal = getObjectVal(showFile);

                if (columnFlat){
                    this.sheet.setColumnWidth(i, getCellWidth(DEFAULT_WIDTH) * 256);
                }

                Cell cell = row.createCell(i);
                cell.setCellStyle(dataStyle);
                cell.setCellType(CellType.STRING);
                cell.setCellValue(fieldVal);
                i++;
            }
            startIndex++;
            columnFlat = false;
        }
        addSelect();
    }

    protected void addSelect(){
        Map<String, Map<String, String>> dictMap = getDict();
        if (cellSelect != null && cellSelect && !CollectionUtils.isEmpty(dictMap)){
            int numberOfSheets = workbook.getNumberOfSheets();
            while (numberOfSheets > 0){
                Sheet sheetAt = workbook.getSheetAt(--numberOfSheets);

                for (int i = 0; i < showFiles.length; i++) {
                    String showFile = showFiles[i];
                    if (dictMap.containsKey(showFile)){
                        //为指定范围内的单元格添加下拉框样式
                        CellRangeAddressList cas = new CellRangeAddressList(title.length, sheetAt.getLastRowNum(), i, i);
                        //创建下拉数据列
                        Map<String, String> dictValues = dictMap.get(showFile);
                        String[] dicts = dictValues.values().stream().toArray(String[]::new);
                        DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(dicts);
                        //将下拉数据放入下拉框
                        HSSFDataValidation dataValidation = new HSSFDataValidation(cas, dvConstraint);
                        dataValidation.createErrorBox("错误", "请选择下拉框的值");
                        dataValidation.setShowErrorBox(false);
                        sheetAt.addValidationData(dataValidation);
                    }
                }
            }
        }

    }

    /**
     * 可重写的标题样式
     */
    protected CellStyle buildTitleStyle(){
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); //背景色
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        HSSFFont headerFont = (HSSFFont) workbook.createFont(); // 创建字体样式
        headerFont.setBold(true); //字体加粗
        headerFont.setFontName("仿宋"); // 设置字体类型
        headerFont.setFontHeightInPoints((short) 12); // 设置字体大小
        cellStyle.setFont(headerFont); // 为标题样式设置字体样式
        return cellStyle;
    }

    /**
     * 可重写的单元格样式
     */
    protected CellStyle buildColumStyle(){
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.LEFT);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边
        HSSFFont headerFont = (HSSFFont) workbook.createFont(); // 创建字体样式
        headerFont.setBold(false); //字体加粗
        headerFont.setFontName("仿宋"); // 设置字体类型
        headerFont.setFontHeightInPoints((short) 11); // 设置字体大小
        cellStyle.setFont(headerFont); // 为标题样式设置字体样式
        return cellStyle;
    }

    public void exportList(){
        ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletResponse response = requestAttributes.getResponse();
        response.reset();
        response.setContentType("text/html;charset=utf-8");
        try {
            response.setHeader("Content-disposition", "attachment; filename="+ new String((fileName).getBytes("gbk"),"iso8859-1") +".xls");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        BufferedOutputStream bufferedOutPut = null;
        ServletOutputStream output = null;
        try {
            output = response.getOutputStream();
            bufferedOutPut = new BufferedOutputStream(output);
            bufferedOutPut.flush();
            workbook.write(bufferedOutPut);
        } catch (IOException e){
            e.printStackTrace();
        } finally{
            if (bufferedOutPut != null){
                try {
                    bufferedOutPut.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

            if (output != null){
                try {
                    output.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            delDict();
        }
    }

    public Workbook getWorkbook() {
        return workbook;
    }

    public void setWorkbook(Workbook workbook) {
        this.workbook = workbook;
    }
}
